package Basic.Dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@SuppressWarnings("unused")
public class BaseDAO extends DBconn {
	protected Connection conn = null;
	protected PreparedStatement stmt = null;
	protected CallableStatement ctmt = null;
	protected ResultSet rs = null;
	
	
	public void close()
	{
		this.close(conn,stmt,rs);
	}
	
	public int insert(String sql){
		int row = 0;
		int primarykey = 0;
		try {
			conn = this.getcConnection();
			stmt =conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			row = stmt.executeUpdate();
			if(row!=0)
			{
				ResultSet rs = stmt.getGeneratedKeys();
				if(rs.next())primarykey = rs.getInt(1);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
			this.close();
		}
		if(primarykey!=0) return primarykey;
		else return row;
	}
	
	
	public int insert(String sql,Object para[])
	{
		int row = 0;
		int primarykey = 0;
		
		try {
			conn = this.getcConnection();
			
			stmt =conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			
			for (int i=1;i<=para.length;i++)
			{
				stmt.setObject(i, para[i-1]);
			}
			row = stmt.executeUpdate();
			
			if(row!=0){
				ResultSet rs = stmt.getGeneratedKeys();
				if(rs.next())primarykey = rs.getInt(1);
			}
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close();
		}
		if(primarykey!=0) 
			return primarykey;
		else 
			return row;
	}
	
	
	public Object executeSelectProduce(String procName){
		try {
			conn = this.getcConnection();
			ctmt = conn.prepareCall("{? = call "+procName+"}");
			ctmt.registerOutParameter(1, java.sql.Types.INTEGER);
			boolean type = ctmt.execute();
			if(type){
				rs = ctmt.getResultSet();
				return rs;
			}else{
				int isSuccess = ctmt.getInt(1);
				return new Integer(isSuccess);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally 
		{
			
		}
		return null;
	}
	
	public Object executeSelectProduce(String procName,Object[]para)
	{
		try {
			conn = this.getcConnection();
			ctmt = conn.prepareCall("{? = call "+procName+"}");
			ctmt.registerOutParameter(1, java.sql.Types.INTEGER);
			for(int i=0;i<para.length;i++)
			{
				ctmt.setObject(i+2, para[i]);
			}
			boolean type = ctmt.execute();
			if(type){
				rs = ctmt.getResultSet();
				return rs;
			}else{
				int isSuccess = ctmt.getInt(1);
				return new Integer(isSuccess);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// TODO Auto-generated catch block
			
		}
		
		return null;	
		
	}
	
	public int update(String sql){
		int row = 0;
			try {
				conn = this.getcConnection();
				PreparedStatement ptmt = conn.prepareStatement(sql);
				
				row = ptmt.executeUpdate();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				// TODO Auto-generated catch block
				this.close();
			}
			
		return row;
		
	}
	
	public int update(String sql,Object[] para){
		int row = 0;
			
		try {
			conn = this.getcConnection();
			PreparedStatement ptmt = conn.prepareStatement(sql);
			for(int i = 1;i<=para.length;i++)
			{
				ptmt.setObject(i, para[i-1]);
			}
			row = ptmt.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// TODO Auto-generated catch block
			this.close();
		}
		return row;
		
	}
	
	public int delete(String sql){
		int row = 0;
			try {
				conn = this.getcConnection();
				PreparedStatement ptmt = conn.prepareStatement(sql);
				
				row = ptmt.executeUpdate();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				// TODO Auto-generated catch block
				this.close();
			}
			
		return row;
		
	}
	
	public int delete(String sql,Object[] para){
		int row = 0;
			
		try {
			conn = this.getcConnection();
			PreparedStatement ptmt = conn.prepareStatement(sql);
			for(int i = 1;i<=para.length;i++)
			{
				ptmt.setObject(i, para[i-1]);
			}
			row = ptmt.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// TODO Auto-generated catch block
			this.close();
		}
		return row;
		
	}
	
	public ResultSet select(String sql)
		{
			try {
				Connection conn = getcConnection();
				PreparedStatement ptmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				rs = ptmt.executeQuery();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				
			}
				return rs;
		
		}
	
	public ResultSet select(String sql,Object[] param)
	{
		try {
			Connection conn = getcConnection();
			PreparedStatement ptmt = conn.prepareStatement
					(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			for(int i=1;i<=param.length;i++){
				ptmt.setObject(i, param[i-1]);
			}
			rs = ptmt.executeQuery();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			
		}
			return rs;
	
	}
	
	public int selectCount(String sql){
		int count = 0;
		try {
			Connection conn = getcConnection();
			PreparedStatement ptmt = 
					conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = ptmt.executeQuery();
			if(rs!=null){
				rs.last();
				count = rs.getRow();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.close();
		}
		
		return count;
		
	}
	
	
	public int selectCount(String sql,Object[] param){
		int count = 0;
		try {
			Connection conn = getcConnection();
			PreparedStatement ptmt = 
					conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			for(int i=1;i<=param.length;i++){
				ptmt.setObject(i, param[i-1]);
			}
			rs = ptmt.executeQuery();
			if(rs!=null){
				rs.last();
				count = rs.getRow();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.close();
		}
		
		return count;
		
	}
	
	public int pageAmount(String sql ,int sizePage ){
		int totalPages = 0;
		try {
			Connection conn = getcConnection();
			PreparedStatement ptmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = ptmt.executeQuery();
			rs.last();
			int row = rs.getRow();
			if(row% sizePage ==0){
				totalPages = row/sizePage;
			}else{
				totalPages = row/sizePage+1;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// TODO Auto-generated catch block
			close();
		}
		return totalPages;
	}
	
	public int pageAmount(String sql ,Object[] param,int sizePage ){
		int totalPages = 0;
		try {
			Connection conn = getcConnection();
			PreparedStatement ptmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			for(int i=1;i<=param.length;i++){
				ptmt.setObject(i, param[i-1]);
			}
			ResultSet rs = ptmt.executeQuery();
			rs.last();
			int row = rs.getRow();
			if(row% sizePage ==0){
				totalPages = row/sizePage;
			}else{
				totalPages = row/sizePage+1;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// TODO Auto-generated catch block
			close();
		}
		return totalPages;
	}
	
	public  ResultSet selectByPage(String sql ,int sizePage,int curPage ){
	
		try {
			Connection conn = getcConnection();
			PreparedStatement ptmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs = ptmt.executeQuery();
			int startRow = (curPage-1)*sizePage+1;
			rs.absolute(startRow);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// TODO Auto-generated catch block
			
		}
		return rs;
		
	}
	public  ResultSet selectByPage(String sql ,Object[] param,int sizePage,int curPage ){
		
		try {
			Connection conn = getcConnection();
			PreparedStatement ptmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			for(int i=1;i<=param.length;i++){
				ptmt.setObject(i, param[i-1]);
			}
			rs = ptmt.executeQuery();
			int startRow = (curPage-1)*sizePage;
			rs.absolute(startRow);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// TODO Auto-generated catch block
			
		}
		return rs;
		
	}
	public static void main (String[] args)
	{
		
	}
}
